using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using System.Runtime.InteropServices;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebPartPages;
using SQLDropDownListFilter.WebPartCode;

namespace SQLDropDownListFilter
{
    [Guid("c00f6e64-dafd-46fe-bf5d-44c2254dbf3f")]
    public class LinkableMOSSDropDownFilter : Microsoft.SharePoint.WebPartPages.WebPart, ITransformableFilterValues, ICascadeValueProvider
    {
        #region Controls
        DropDownList _dropDown;
        Label _label;
        #endregion

        #region Configurable Vars
        string _filterLabel = null;
        string _SqlQuery = null;
        string _ConnectionStringConfigKey = string.Empty;
        bool _IncludeAll = false;
        bool _DisableWhenParentAll = false;
        bool _SQLInConfigEntry = true;
        #endregion

        #region Member Vars
        private string connectionString = string.Empty;
        #endregion

        #region Configurable Properties
        [Personalizable(PersonalizationScope.Shared)]
        [WebBrowsable(true)]
        [System.ComponentModel.Category("Filter Settings")]
        [WebDisplayName("Connection String Config Key")]
        [WebDescription("The config entry key that holds the connection string.")]
        public string ConnectionStringConfigKey
        {
            get
            {
                return _ConnectionStringConfigKey;
            }
            set { _ConnectionStringConfigKey = value; }
        }

        [Personalizable(PersonalizationScope.Shared)]
        [WebBrowsable(true)]
        [System.ComponentModel.Category("Filter Settings")]
        [WebDisplayName("Store SQL In Config Entry")]
        [WebDescription("Indicates that SQL will be stored in config entry.  If checked use SQL Query field to name the config entry.")]
        public bool SQLInConfigEntry
        {
            get
            {
                return _SQLInConfigEntry;
            }
            set { _SQLInConfigEntry = value; }
        }

        [Personalizable(PersonalizationScope.Shared)]
        [WebBrowsable(true)]
        [System.ComponentModel.Category("Filter Settings")]
        [WebDisplayName("SQL Query")]
        [WebDescription("The SQL Query that will be used to fill the drop down.  Should return either [value] or [value],[label].  If you are sending a trim value into this from another web part, include \"{0}\" in the where clause.")]
        public string SqlQuery
        {
            get
            {
                if (_SqlQuery == null)
                {
                    _SqlQuery = "SELECT 'Not Configured' as Value";
                }
                return _SqlQuery;
            }
            set { _SqlQuery = value; }
        }

        [Personalizable(PersonalizationScope.Shared)]
        [WebBrowsable(true)]
        [System.ComponentModel.Category("Filter Settings")]
        [WebDisplayName("Label")]
        [WebDescription("The label you wish to show next to the drop down list.")]
        public string FilterLabel
        {
            get
            {
                if (_filterLabel == null)
                {
                    _filterLabel = "";
                }
                return _filterLabel;
            }
            set { _filterLabel = value; }
        }

        [Personalizable(PersonalizationScope.Shared)]
        [WebBrowsable(true)]
        [System.ComponentModel.Category("Filter Settings")]
        [WebDisplayName("Include All")]
        [WebDescription("Include an all Item at the top of the list")]
        public bool IncludeAll
        {
            get
            {
                return _IncludeAll;
            }
            set { _IncludeAll = value; }
        }

        [Personalizable(PersonalizationScope.Shared)]
        [WebBrowsable(true)]
        [System.ComponentModel.Category("Filter Settings")]
        [WebDisplayName("Disable on Parent All")]
        [WebDescription("Disable this filter if parent is \"All\"")]
        public bool DisableWhenParentAll
        {
            get
            {
                return _DisableWhenParentAll;
            }
            set { _DisableWhenParentAll = value; }
        }
        #endregion

        #region SelectedValueConnections
        [ConnectionProvider("SelectedValue", "f8189fb5-c2bc-4db8-9cce-30091f15d08c", AllowsMultipleConnections = true)]
        public ITransformableFilterValues SetConnection()
        {
            return this;
        }
        #endregion

        #region FilterValueConnections
        [ConnectionProvider("FilterValue", "a545f75f-7d97-432f-bc81-b797d369d1bb", AllowsMultipleConnections = true)]
        public ICascadeValueProvider SetCascadeConnection()
        {
            return this;
        }

        [ConnectionConsumer("FilterValue", "bb81fe11-3925-41ce-a54d-952bfc06af1d", AllowsMultipleConnections = true)]
        public void SetWhereFilter(ICascadeValueProvider defaultFilterValue)
        {
            EnsureChildControls();
            string filterValue = ViewState["filterValue"] as string;
            if (filterValue == null || filterValue != defaultFilterValue.FilterValue)
            {
                ViewState["filterValue"] = defaultFilterValue.FilterValue;
                SaveViewState();
                PopulateList();
            }
        }

        #endregion

        #region DefaultValueConnections
        [ConnectionConsumer("DefaultValue", "08823fe7-fb3b-4646-9155-cac090a1dcee", AllowsMultipleConnections = true)]
        public void SetDefaultValue(IDefaultFilterValue defaultFilterValue)
        {
            EnsureChildControls();
            if (_dropDown.Items.FindByValue(defaultFilterValue.DefaultValue) != null)
                _dropDown.SelectedValue = defaultFilterValue.DefaultValue;
        }
        #endregion

        protected override void CreateChildControls()
        {
            base.CreateChildControls();
            try
            {

                if (SPContext.Current.Web.Properties.ContainsKey(ConnectionStringConfigKey))
                {
                    connectionString = SPContext.Current.Web.Properties[ConnectionStringConfigKey];
                }

                if (connectionString == "Set Connection String HERE." || connectionString == string.Empty)
                {
                    this.Controls.AddLiteral("Connection string not set");
                }
                else
                {
                    Table table = new Table();
                    TableRow tableRow = new TableRow();
                    TableCell tableCell = new TableCell();

                    table.Rows.Add(tableRow);
                    tableRow.Cells.Add(tableCell);

                    _label = new Label();
                    _label.ID = "Label";
                    _label.Text = _filterLabel;
                    tableCell.Controls.Add(_label);

                    tableCell = new TableCell();

                    _dropDown = new DropDownList();
                    _dropDown.ID = "DropDown";
                    _dropDown.AutoPostBack = true;
                    tableCell.Controls.Add(_dropDown);

                    tableRow.Cells.Add(tableCell);
                    this.Controls.Add(table);

                    if (!Page.IsPostBack) PopulateList();
                }
            }
            catch (Exception ex)
            {
                ex.LogToSharepoint(true);
                this.Controls.Clear();
                this.Controls.AddLiteral("Critical Error when building control.  Please review SharePoint logs for more information.");
            }
        }

        private void PopulateList()
        {
            try
            {
                string selectCommand = SqlQuery;
                string filterValue = ViewState["filterValue"] as string;

                if (SQLInConfigEntry)
                    if (SPContext.Current.Web.Properties.ContainsKey(SqlQuery))
                    {
                        selectCommand = SPContext.Current.Web.Properties[SqlQuery];
                    }
                    else
                    {
                        _dropDown.Items.Clear();
                        _dropDown.Items.Add("Not Configured");
                        return;
                    }

                if (selectCommand.Contains("{0}"))
                {
                    if (filterValue != null)
                        selectCommand = string.Format(selectCommand, filterValue);
                }

                _dropDown.Items.Clear();
                if (IncludeAll) _dropDown.Items.Add(new ListItem("All", "All"));
                using (SqlConnection connection = new SqlConnection(connectionString))
                {

                    connection.Open();

                    using (SqlCommand command = connection.CreateCommand())
                    {
                        command.CommandText = selectCommand;
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                if (reader.FieldCount == 2)
                                {
                                    _dropDown.Items.Add(new ListItem(reader[1].ToString(), reader[0].ToString()));
                                }
                                else
                                {
                                    _dropDown.Items.Add(new ListItem(reader[0].ToString()));
                                }
                            }
                        }
                    }
                }
                _dropDown.Enabled = !(filterValue == "All" && DisableWhenParentAll);
            }
            catch (Exception ex)
            {
                ex.LogToSharepoint(true);
                this.Controls.Clear();
                this.Controls.AddLiteral("Critical Error when retrieving control data.  Please review SharePoint logs for more information.");
            }
        }

        #region ITransformableFilterValues Members

        public bool AllowAllValue
        {
            get { return false; }
        }

        public bool AllowEmptyValue
        {
            get { return false; }
        }

        public bool AllowMultipleValues
        {
            get { return false; }
        }

        public string ParameterName
        {
            get
            {
                if (string.IsNullOrEmpty(_filterLabel))
                    return "Selected Value";
                else
                    return _filterLabel + " Selected Value";
            }
        }

        public System.Collections.ObjectModel.ReadOnlyCollection<string> ParameterValues
        {
            get
            {
                return new ReadOnlyCollection<string>(new List<string>(new string[] { FilterValue }));
            }
        }

        #endregion

        #region IDefaultFilterValue Members

        public string DefaultValue
        {
            get
            {

                EnsureChildControls();
                if (_dropDown == null) return string.Empty;
                else return _dropDown.SelectedValue;
            }
        }

        #endregion

        #region ICascadeValueProvider Members

        public string FilterValue
        {
            get
            {
                EnsureChildControls();
                if (_dropDown == null) return string.Empty;
                else return _dropDown.SelectedValue;
            }
        }

        #endregion
    }
}